package com.direct.common;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


/**
 * 数据库工具类
 * @author An Hui
 * @date 2015年3月4日 上午10:27:29
 * @version 1.0
 */
public class DataBaseUtils {
	/**
	 * 获取链接
	 * @return
	 * @throws SQLException
	 * @throws IOException 
	 */
	
	
	public static Connection getConnection() throws SQLException{
		Properties pro = new Properties();
		try {
			pro.load(DataBaseUtils.class.getClassLoader().getResourceAsStream("newdatabase.propreties"));
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		String jdbc = pro.getProperty("jdbc");
		String url = pro.getProperty("url");
		String user = pro.getProperty("user");
		String password = pro.getProperty("password");
		try {
			Class.forName(jdbc);
			return DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			throw new SQLException("找不到数据库驱动", e);
		}
	}
	
	/**
	 * 关闭链接
	 * @param conn
	 * @param sta
	 * @param res
	 * @throws SQLException 
	 */
	public static void close(Connection conn, Statement pst, ResultSet res) throws SQLException {
		if(res != null) {
			res.close();
		}
		if(pst != null) {
			pst.close();
		}
		if(conn != null) {
			conn.close();
		}
	}
	
	/**
	 * 关闭连接
	 * @param conn
	 * @throws SQLException
	 */
	public static void close(Connection conn) throws SQLException {
		close(conn, null);
	}
	/**
	 * 重载的关闭链接
	 * @param conn
	 * @param sta
	 * @throws SQLException 
	 */
	public static void close(Connection conn, Statement sta) throws SQLException {
		close(conn, sta, null);
	}
	
	/**
	 * 执行更新语句
	 * @param sql
	 * @param params
	 * @throws SQLException 
	 */
	public static void executeUpdate(String sql, Object[] params) throws SQLException {
		Connection conn = null;
		PreparedStatement sta = null;
		try{
			conn = getConnection();
			sta = conn.prepareStatement(sql);
			// 设置参数
			if(params != null && params.length > 0) {
				for(int i=0;i<params.length;i++) {
					
					Object p = params[i];
					if(p instanceof java.util.Date) {
						// 将util.Date转成sql.Date
						params[i] = new java.sql.Timestamp(((java.util.Date) p).getTime());
					}
					sta.setObject(i + 1, params[i]);
				}
			}
			// 执行
			sta.executeUpdate();
		} finally {
			close(conn, sta);
		}
		
	}
	
	/**
	 * 重载的更新操作
	 * @param sql
	 * @param params
	 * @throws SQLException
	 */
	public static void executeUpdate(String sql, List<Object> params) throws SQLException {
		executeUpdate(sql, params.toArray());
	}
	
	/**
	 * 判断查询结果是否存在
	 * @param sql
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public static boolean checkExist(String sql, Object...params)throws SQLException{
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet res = null;
		try {
			conn =getConnection();
			sta = conn.prepareStatement(sql);
			// 绑定参数
			if(params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					Object p = params[i];
					if(p instanceof java.util.Date) {
						sta.setObject(i + 1, new java.sql.Date(((java.util.Date) p).getTime()));
					} else {
						sta.setObject(i + 1, params[i]);
					}
				}
			}
			// 执行查询
			res = sta.executeQuery();
			// 判断是否存在
			if(res.next()) {
				return true;
			}else{
				return false;
			}
		} finally {
			DataBaseUtils.close(conn, sta, res);
		}
	}
	
	public static List<Object> getColum (String sql, Object...params)throws SQLException{
		Connection conn = null;
		PreparedStatement sta = null;
		ResultSet res = null;
		List< Object> ls = new ArrayList<Object>();
		try {
			conn =getConnection();
			sta = conn.prepareStatement(sql);
			// 绑定参数
			if(params != null && params.length > 0) {
				for (int i = 0; i < params.length; i++) {
					Object p = params[i];
					if(p instanceof java.util.Date) {
						sta.setObject(i + 1, new java.sql.Date(((java.util.Date) p).getTime()));
					} else {
						sta.setObject(i + 1, params[i]);
					}
				}
			}
			// 执行查询
			res = sta.executeQuery();
			// 判断是否存在
			while(res.next()) {
				ls.add(res.getObject(1));
			}
		} finally {
			DataBaseUtils.close(conn, sta, res);
		}
		return ls;
	}
}
